import json
import logging
import sys
import threading
import os

from utils import custom_utils

sys.path.insert(0, '..')
from mysite import db_config
from utils.ThreadPoolFactory import CustomDbEngines

os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
os.environ['ORACLE_HOME'] = '/data/oracle/app/11.2.4'
logger = logging.getLogger('log')


class Check(object):
    def __init__(self, company, version, username):
        self.company = company
        self.version = version
        self.username = username
        self.rerun = False

    def init_table(self):
        """
        类实例化所需参数
        :param company:         公司名
        :param source_system:   源系统名称
        :return:
        初始化检核表
            如果check_result_{0}表存在，则从check_result_template表中插入对应公司检核项和逻辑
            如果check_result_{0}表不存在，则使用check_result_template表作为模板新建
        """
        company = self.company

        logging.info('*' * 50)
        logging.info(f'开始初始化检核结果表...check_result_{company}')

        conn = db_config.mysql_connect()
        curs = conn.cursor()
        curs.execute('set autocommit=0')

        # sql = f"select table_name from information_schema.tables where table_schema='data_quality' and table_name='check_result_{company}'"
        # table_count = curs.execute(sql)
        try:
            sql = f"""create table if not exists check_result_{company} as select * from check_result_template
                      where company='{company}' and id is null """
            sql_detail = f""" create table if not exists check_result_detail_{company} 
            as select * from check_result_detail_template where id is null
            """
            # 创建主表
            curs.execute(sql)
            # 创建明细表
            curs.execute(sql_detail)
            # 获取检核版本号 通过定时过来的任务 没有版本号  自动生成
            if self.version is None:
                curs.execute(f"select coalesce(max(version),0) from check_result_version where company='{company}'")
                row = curs.fetchone()
                self.version = row[0] + 1 if row else 1
            else:
                self.rerun = True

            # 重新执行时  需要将当前版本号数据进行删除
            if self.rerun:
                delete_sql = f"""
                delete from check_result_{company} where company='{company}' and check_version={self.version}
                """
                delete_detail = f"""delete from check_result_detail_{company} where company='{company}'
                 and check_version={self.version} """
                curs.execute(delete_sql)
                curs.execute(delete_detail)
                conn.commit()

            exe_sql = f"""
            insert into check_result_{company}(
                id,company,source_system,check_item,target_table,
                risk_market_item,problem_type,check_sql,
                problem_id,item_count,problem_count,
                problem_per,db,note,status,update_flag,check_date,
                check_version,check_log
              ) 
            select  id,company,source_system,check_item,target_table,
                risk_market_item,problem_type,check_sql,
                problem_id,item_count,problem_count,
                problem_per,db,note,status,'N',null,
                '{self.version}',check_log from check_result_template 
            where company='{company}' 
            and  id not in (select id from check_result_{company} where check_version={self.version})
            """
            curs.execute(exe_sql)

            exe_sql_detail = f""" insert into  check_result_detail_{company}(id,category,category_order,pid,
            t_name,c_name,start_time,end_time,check_sql,effective,result,check_version,company,
            denominator_order,type
            ) 
            select id,category,category_order,pid,t_name,c_name,start_time,end_time,check_sql,
            effective,result,'{self.version}',company ,denominator_order,type
            from check_result_detail_template where company='{company}'  
            and id not in (select a.id from check_result_detail_{company}  a where check_version={self.version} )
            """
            curs.execute(exe_sql_detail)

            conn.commit()
            logging.info('*' * 50, f"初始化 check_result_{company}表 ...完成", '*' * 50)
            return True
        except Exception as e:
            conn.rollback()
            logging.error('!' * 50, f'初始化 check_result_{company}表 ...失败,错误信息：{str(e)}', '!' * 50)
            return False
        finally:
            curs.close()
            conn.close()

    def run_check(self):
        """
        执行检核
        类实例化所需参数
        :return:
        """
        company = self.company
        logging.info('-' * 50)
        logging.info("正在检核" + company + "数据...")
        try:
            mysql_conn = db_config.mysql_connect()
            mysql_curs = mysql_conn.cursor()
            mysql_curs.execute('set autocommit=1')
            # 插入版本信息
            self.add_check_version(company, mysql_conn, mysql_curs)
            # 获取待处理的 检验数据汇总项
            check_list = self.get_check_list(company, mysql_curs)
            # 执行检核  由于检查sql可能耗时比较久，所以不考虑频繁提交问题， 只需要调整commit位置即可。
            for i in check_list:
                logger.info(f'{company},id={i[0]}, note={i[4]}  >>>开始检核')
                data_id = i[0]
                # 获取一个检查项的sql执行引擎
                try:
                    engine = CustomDbEngines.get_engine(i[2])
                    exec_cur_sql_conn = engine.raw_connection()
                except Exception as e:
                    logger.error(str(e))
                    self.update_result_status(company, mysql_curs, data_id, str(e), '{}')
                    continue
                # 执行具体的子sql并获取子sql执行结果
                result_json = self.exe_sub_sql(company, exec_cur_sql_conn, mysql_curs, data_id)
                # 更新父级数据
                self.update_result_status(company, mysql_curs, data_id, 'success', result_json)
                logger.info(f'{company},id={i[0]}, note={i[4]}  >>>完成检核')
            self.insert_exec_log(company, mysql_curs)
            logging.info("-" * 25, f'{company} 检核完成', "-" * 25)
            return True
        except Exception as e:
            logging.error(f"""{company} 检核出错,错误信息：{e}""")
            return False
        finally:
            mysql_curs.close()
            mysql_conn.close()

    def update_result_status(self, company, curs, data_id, log_str, result_json):
        archive_sql = f""" update check_result_{company} 
                                            set check_log = "{log_str}",
                                            result_set = '{result_json}',
                                            update_flag='Y'
                                            where id={data_id}
                                            and check_version={self.version}
                                            and company = '{company}'
                                """
        curs.execute(archive_sql)

    def insert_exec_log(self, company, curs):
        log_sql = f"""
                  insert into check_execute_log(status,company,execute_date,execute_user,db,check_version)
                  values('success','{company}',now(),'{self.username}','',{self.version})"""
        curs.execute(log_sql)

    def exe_sub_sql(self, company, exec_cur_sql_conn, mysql_curs, data_id):
        """
        获取子sql并返回json结果
        :param company:
        :param exec_cur_sql_conn:
        :param mysql_curs:
        :param data_id:
        :return:
        """
        # 获取配置参数
        sql_check_rule_sql = f""" 
        select id,rule_key,rule_value,company,effective from check_rule_config where company='global' and effective=1
        union all 
        select id,rule_key,rule_value,company,effective from check_rule_config where company='{company}' and effective=1
"""
        mysql_curs.execute(sql_check_rule_sql)
        sql_check_rule = custom_utils.arr_to_dictarr(mysql_curs.fetchall(), mysql_curs.description)

        result_set = []
        sql_sub = """select id,category,pid,t_name,c_name,start_time,
                    end_time,check_sql,effective,result,check_version,company,
                    denominator_order,type,category_order  from check_result_detail_{company} where pid = {pid} and check_version={version}""" \
            .format(pid=data_id, company=company, version=self.version)
        mysql_curs.execute(sql_sub)
        check_detail_list = mysql_curs.fetchall()
        for detail in check_detail_list:
            result_dict = {'id': detail[0], 'denominator_order': detail[12], 'type': detail[13],
                           'category_order': detail[14]}
            check_sql = detail[7]

            item_count = 0
            check_log = ''
            t_check_sql = check_sql.lower()
            # 检查sql  是否包含 删除等关键字
            if check_delete(t_check_sql):
                continue
            try:
                with exec_cur_sql_conn.cursor() as curs_source:
                    # 替换sql 中的 占位符
                    check_sql = self.build_exec_sql(check_sql, sql_check_rule)
                    logger.info(f'子sql {check_sql} 开始检核')
                    curs_source.execute(check_sql)
                    check_result = curs_source.fetchall()  # 检核结果
                    for t in check_result:
                        item_count = t[0]
            except Exception as e:
                check_log = str(e)
                logger.error(check_log)

            archive_sql = f""" update check_result_detail_{company} 
                                            set check_log = '{check_log}',
                                            result = '{item_count}'
                                            where id={detail[0]}
                                            and check_version={self.version}
                                            and company = '{company}'
                                """
            mysql_curs.execute(archive_sql)
            result_dict['item_count'] = item_count
            result_set.append(result_dict)
            logger.info(f'子sql  完成检核')
        return json.dumps(result_set)

    def build_exec_sql(self, check_sql, sql_check_rule):
        for check_rule in sql_check_rule:
            replace_key = check_rule['rule_key']
            replace_val = check_rule['rule_value']
            check_sql = check_sql.replace("${" + str(replace_key) + "}", replace_val)
        return check_sql

    def get_check_list(self, company, curs):
        sql = f"""select a.id,a.check_sql,b.connection_string,b.db_type ,a.note
                    from check_result_{company} a , source_db_info b 
                    where a.db = b.id
                    and a.company='{company}'
                    and update_flag = 'N'
                    and a.status='已启用'
                    and check_version={self.version}
                    order by id"""
        # 从规则库表中取出检核项和检核sql，只运行“已启用”状态的SQL
        curs.execute(sql)
        check_list = curs.fetchall()
        return check_list

    def add_check_version(self, company, conn, curs):
        insert_version = f"""
              insert into check_result_version (company,version,check_date) values ('{company}',{self.version},now())
            """
        check_version = f"""
              select count(1) from  check_result_version where company ='{company}' and version ={self.version}
            """
        curs.execute(check_version)
        row = curs.fetchone()
        if row[0] == 0:
            curs.execute(insert_version)
        # conn.commit()

    def calc_result(self, version):
        """根据检核结果明细计算问题占比
        1. 填充空值的问题占比
        2. 计算正常的问题占比
        
        :param version:     要进行计算的版本号
        :return:            检核成功返回True，失败返回False
        """
        company = self.company
        try:
            conn = db_config.mysql_connect()
            curs = conn.cursor()
            curs.execute('set autocommit=0')
            # 计算问题占比
            # 处理item_count和problem_count都是null或=0的行
            sql = f"""update check_result_{company}
                        set problem_per=100
                        where (item_count is null or item_count=0)
                        and check_version={version}"""
            curs.execute(sql)

            # 计算正常的问题占比
            sql = f"""update check_result_{company} set problem_per=problem_count/item_count*100\
                        where problem_per is null
                        and check_version={version}"""
            curs.execute(sql)
            conn.commit()
            return True
        except Exception as e:
            conn.rollback()
            return False
        finally:
            curs.close()
            conn.close()


class MyThread(threading.Thread):
    """重新定义带返回值的线程类"""

    def __init__(self, func, args=()):
        super(MyThread, self).__init__()
        self.func = func
        self.args = args

    def run(self):
        self.result = self.func(*self.args)

    def get_result(self):
        try:
            return self.result
        except Exception:
            return None


def check_delete(t_check_sql):
    return 'delete' in t_check_sql or 'update' in t_check_sql or \
           'truncate' in t_check_sql or 'drop' in t_check_sql or 'create' in t_check_sql
